USE GESTION2
go
IF OBJECT_ID('dbo.WASP_D0005_CAPPF000') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.WASP_D0005_CAPPF000
    IF OBJECT_ID('dbo.WASP_D0005_CAPPF000') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.WASP_D0005_CAPPF000 >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.WASP_D0005_CAPPF000 >>>'
END
go
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE WASP_D0005_CAPPF000
AS

BEGIN
select 
	CAST(substring(A.RUTTRABAJA,1,len(RUTTRABAJA)-1) AS REAL)as RUTTRABAJA,
 	substring(A.RUTTRABAJA,len(RUTTRABAJA),1) as DVETRABAJA,
 	convert (decimal ,a.CODAGENCIA) as CODAGENCIA,
 	a.CLVEMPRESA ,
 	a.RUTEMPRESA,
 	a.NOMTRABAJA,
	cast(convert(varchar(8),d.FINGRTRABA,112) as decimal)as FINGRTRABA,
	f.z07354cod as Tipo_contrato ,
	null as FCONTRABA,
 	cast(convert(varchar(8),a.FNACITRABA,112) as decimal)as FNACITRABA,
 	a.SEXOTRABAJ,
 	c.tipo as TIPTRABAJA,
 	null as STSCSOTRAB,
 	null as STSSILTRAB,
 	null as STSCARTRAB,
 	null as FCESATRABA,
 	null as STSCESTRAB,
 	null as PRIORIDTRA,
 	a.ESTADOTRAB,
 	c.caja as CAJPREVTRA,
 	null as FCAMBEMTRA,
 	null as CLVEMPANTE,
	b.importe as RENTATRABA,
 	null as TRAMOTRA01,
 	null as TRAMOTRA02,
 	null as TRAMOTRA03,
 	null as TRAMOTRA04,
 	null as TRAMOTRA05,
 	null as TRAMOTRA06,
 	null as TRAMOTRA07,
 	null as TRAMOTRA08,
 	null as TRAMOTRA09,
 	null as TRAMOTRA10,
	a.DIRTRABAJA,
	e.z07310DSC as COMUNATRAB,
	NULL AS RSTOFILLER,
	NULL AS FMODIFREGT,
	NULL AS STSMODREGT,
	NULL AS USRMODREGT,
	a.Z07369CT1 as CTABT
into  dbo.AS_CAPPF000

from
   cappf000_temp a Left Join mt_rentas b ON
     	a.Ctnro = b.cta 
   Left Join mt_tipo_caja_fecha c ON
      a.CLVEMPRESA = c.emp and 
      a.Ctnro = c.cta
   Left Join mt_fecha_ingreso d ON
      a.ctnro = d.ctnro 
   Left Join z07310 e ON
    	a.comunatrab = e.z07310com
   Left Join tip f  ON
    	a.Z07369CT1 = f.Z07374CT1 and
	    a.Ctnro = f.Z07374CT2                      	
/*
 	cappf000_temp a (nolock), 
 	mt_rentas b (nolock),
 	mt_tipo_caja_fecha c (nolock),
 	mt_fecha_ingreso d (nolock),
	z07310 e (nolock),
	tip f
*/	
--where
-- 	a.Ctnro *= b.cta and 
-- 	a.CLVEMPRESA *= c.emp and 
--  a.Ctnro *= c.cta and
-- 	a.ctnro *= d.ctnro and
--	a.comunatrab *= e.z07310com and
--	a.Z07369CT1 *= f.Z07374CT1 and
--	a.Ctnro *= f.Z07374CT2 
END
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
IF OBJECT_ID('dbo.WASP_D0005_CAPPF000') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.WASP_D0005_CAPPF000 >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.WASP_D0005_CAPPF000 >>>'
go
